PixelUp Agency — Lesson 2

Follow the Money

This lesson you'll build PixelUp's revenue model — the spreadsheet that tells each creator exactly how much they earned last month, and why.

💰

Three Revenue Streams to Model

📺

Ad Revenue

Views × CPM ÷ 1000

🤝

Sponsorships

Deals × Value per deal

👕

Merchandise

Units sold × Profit per unit

New concept this lesson: Absolute cell references — the trick that turns a static table into a live financial model. Change one input cell → every revenue figure recalculates automatically.

Task 1

Import the Revenue Data

Import this lesson's starter.csv into a new Google Sheets tab.

📂

Import starter.csv

1

Create a new Google Sheet (or use a new tab)

Name it "PixelUp Revenue Model". If you're continuing from last lesson, click the + button at the bottom to add a new sheet tab and name it "Revenue".

2

Import the L2 starter.csv

Click , upload the starter.csv from the L2 folder. Choose Replace current sheet and Comma as separator.

3

Check your columns

You should have 7 columns: Creator, Platform, Monthly Views, Sponsorship Deals, Sponsorship Value (£), Merch Sales (units), Merch Profit Per Unit (£). Note your column letters — you'll need them when writing formulas.

4

Add a gap row and set up your input area

Click on row 1 and insert a blank row above your headers. In cell A1 type CPM Rate (£) and in B1 type 8.00 — this is our ad rate input. Format B1 as currency.

💡 Tip: After adding the input row, your data will now start in row 2 (headers) and row 3 onwards (creator data). Your column letters stay the same. Adjust your formula row numbers accordingly.

Task 2

Name Your Input Cell

Before writing any formula, name your CPM Rate cell. This makes every formula readable.

🏷️

Name cell B1 as "CPM_Rate"

1

Click on cell B1

This is where your CPM rate value (8.00) is stored.

2

Click the Name Box

The Name Box is the small box in the top-left of Google Sheets that currently shows "B1". Click directly on it so the text is selected.

3

Type the name and press Enter

Type CPM_Rate (no spaces — use underscore) and press Enter. The cell is now named. If you click B1 again, you'll see "CPM_Rate" in the Name Box instead of "B1".

⚠️ No spaces in named ranges. Google Sheets won't accept spaces in range names. Always use underscores: CPM_Rate not CPM Rate.

Task 3

Ad Revenue Formula

Write the formula that calculates how much each creator earns from ads. Use an absolute reference to your CPM Rate cell.

📺

Build the Ad Revenue column

1

Add a new column header

In the first empty column after your data (likely column H), add a header: "Ad Revenue (£)".

2

Write the formula for @ZaraPlays

In the cell below your new header (row 3 if you added the input row), write:

=C3*$B$1/1000

Where C3 = Monthly Views for @ZaraPlays and $B$1 = your CPM Rate (the $ signs lock this reference).

3

Alternative: use the named range

Instead of $B$1, you can write:

=C3*CPM_Rate/1000

Both work identically — the named version is easier to read.

4

Copy the formula down for all 5 creators

Click the cell with your formula, then grab the small blue square in the bottom-right corner and drag it down to row 7 (covering all 5 creators). The $B$1 stays fixed; only the row number changes (C3, C4, C5...).

5

Format the column as currency

Select the entire Ad Revenue column. Go to .

💡 Test it: Change the value in B1 from 8.00 to 12.00. Every ad revenue figure should update instantly. Change it back to 8.00. This is the power of absolute references — one change, all formulas update.

⚠️ TikTok note: @CoastlineEats is on TikTok where CPM is genuinely much lower (around £0.03 per 1000 views, not £8). For simplicity, we're applying the same CPM rate to all creators in this model. In the extension task, you'll fix this with per-platform CPM values.

Task 4

Sponsorship & Merch Revenue

Add the remaining two revenue streams. These are simpler formulas — multiplication of two columns in the starter data.

🤝

Sponsorship Revenue

1

Add a "Sponsorship Revenue (£)" column header

Add this in the next empty column after Ad Revenue.

2

Write the formula

Sponsorship Revenue = Number of deals × Value per deal. In your starter data, column D = Sponsorship Deals and column E = Sponsorship Value (£).

=D3*E3

Copy this formula down for all 5 creators. Format as currency.

👕

Merch Revenue

3

Add a "Merch Revenue (£)" column header

In the next column after Sponsorship Revenue.

4

Write the formula

Merch Revenue = Units sold × Profit per unit. In your starter data, column F = Merch Sales and column G = Merch Profit Per Unit (£).

=F3*G3

Copy down for all 5 creators. Format as currency.

Tasks 5 & 6

Total Revenue & the Big Test

Bring it all together — then test whether your model actually works by changing the CPM rate.

💷

Task 5 — Total Monthly Revenue

1

Add a "Total Monthly Revenue (£)" column

In the next empty column after Merch Revenue.

2

Write the total formula

Add all three revenue streams. Assuming Ad Revenue is in H, Sponsorship in I, Merch in J:

=H3+I3+J3

Or use SUM:

=SUM(H3:J3)

Copy down for all 5 creators.

🧪

Task 6 — The Live Model Test

3

Change the CPM Rate

Click on cell B1 and change the value from 8.00 to 12.00. Watch every Ad Revenue and Total Revenue figure update instantly. This is a working financial model.

4

Answer these questions

At CPM = £8.00, who earns the most total monthly revenue? Does this surprise you?

At CPM = £12.00, how much does @TechWithTeo earn from ads alone? Does the ad revenue now matter more?

Extension Task

Going Further

Finished everything? This extension makes the model more realistic by giving each platform its own CPM rate.

Extension

Per-Platform CPM Rates

Right now, all creators use the same CPM rate — which isn't realistic. TikTok CPM is genuinely around £0.03, not £8. Improve the model:

Add three input cells: YouTube_CPM = £8.00, TikTok_CPM = £0.03, Instagram_CPM = £1.50
Modify your Ad Revenue formula to use the correct CPM based on each creator's platform
Hint: you'll need an IF formula — we cover this in Lesson 3, but try it now: =IF(B3="YouTube", C3*YouTube_CPM/1000, IF(B3="TikTok", C3*TikTok_CPM/1000, C3*Instagram_CPM/1000))
How does @CoastlineEats' ad revenue change with a realistic TikTok CPM?
Add a "Revenue per Subscriber" column: Total Revenue ÷ Subscribers (you'll need to bring the subscriber data from your L1 sheet)

🤔

Critical thinking

If you were advising @CoastlineEats, what would you suggest to increase their monthly revenue? What does the model tell you?

💷

Lesson 2 Complete

Revenue Model — Built!

You've built a real financial model. Next lesson: what happens if one of our creators goes viral?

✅

What you learned today

How creators earn money from ads (CPM), sponsorships, and merchandise
What CPM means and how to calculate ad revenue from it
The difference between relative and absolute cell references
How to use $B$1 to lock a cell reference in a formula
How to name a cell range in Google Sheets
How to build a financial model with separate input and calculation areas

Coming up in L3: One of our creators' videos goes viral — views triple overnight. We'll use IF statements, MAX, and MIN to model what this means for revenue, and flag which creators are on track to hit their targets.